# -*- coding: utf-8 -*-
"""
均线粘合分析报告生成器
生成简洁的均线粘合分析报告
"""
import logging
import pandas as pd
from datetime import datetime
from database import DatabaseManager
from config import DATABASE_CONFIG

# 配置日志
# 获取项目根目录
PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
LOG_DIR = os.path.join(PROJECT_ROOT, 'logs')
os.makedirs(LOG_DIR, exist_ok=True)

logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s [%(levelname)s] %(message)s',
    handlers=[
        logging.StreamHandler(),
        logging.FileHandler(os.path.join(LOG_DIR, 'ma_convergence_report.log'), encoding='utf-8')
    ]
)
logger = logging.getLogger(__name__)

class MAConvergenceReporter:
    """均线粘合分析报告器"""
    
    def __init__(self):
        self.db = DatabaseManager(DATABASE_CONFIG)
    
    def get_convergence_summary(self, convergence_threshold: float = 0.05) -> dict:
        """获取均线粘合汇总信息"""
        sql = """
        WITH stock_ma AS (
            SELECT 
                sd.stock_code,
                sd.trade_date,
                sd.close_price,
                sd.volume,
                sd.change_pct,
                -- 计算5日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                ) AS ma5,
                -- 计算10日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS ma10,
                -- 计算20日移动平均线
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
                ) AS ma20,
                -- 为每个股票标记最新日期
                ROW_NUMBER() OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date DESC
                ) AS rn
            FROM new_stock_daily sd
            INNER JOIN new_stock_info si ON sd.stock_code = si.stock_code
            WHERE si.status = 'active'
        ),
        convergence_calc AS (
            SELECT 
                sm.stock_code,
                si.stock_name,
                si.exchange,
                si.industry,
                sm.close_price AS current_price,
                ROUND(sm.ma5, 2) AS ma5,
                ROUND(sm.ma10, 2) AS ma10,
                ROUND(sm.ma20, 2) AS ma20,
                sm.change_pct,
                sm.volume,
                sm.trade_date,
                -- 计算粘合度
                ROUND((GREATEST(sm.ma5, sm.ma10, sm.ma20) - LEAST(sm.ma5, sm.ma10, sm.ma20)) / 
                      GREATEST(sm.ma5, sm.ma10, sm.ma20) * 100, 2) AS convergence_ratio,
                -- 判断均线排列顺序
                CASE 
                    WHEN sm.ma5 > sm.ma10 AND sm.ma10 > sm.ma20 THEN '多头排列'
                    WHEN sm.ma5 < sm.ma10 AND sm.ma10 < sm.ma20 THEN '空头排列'
                    ELSE '震荡排列'
                END AS ma_arrangement
            FROM stock_ma sm
            INNER JOIN new_stock_info si ON sm.stock_code = si.stock_code
            WHERE sm.rn = 1
            AND sm.ma5 IS NOT NULL 
            AND sm.ma10 IS NOT NULL 
            AND sm.ma20 IS NOT NULL
        )
        SELECT 
            COUNT(*) as total_convergence_stocks,
            AVG(convergence_ratio) as avg_convergence_ratio,
            MIN(convergence_ratio) as min_convergence_ratio,
            MAX(convergence_ratio) as max_convergence_ratio,
            SUM(volume) as total_volume,
            AVG(volume) as avg_volume,
            COUNT(CASE WHEN convergence_ratio <= 1 THEN 1 END) as strong_convergence_count,
            COUNT(CASE WHEN convergence_ratio <= 2 THEN 1 END) as medium_convergence_count,
            COUNT(CASE WHEN ma_arrangement = '多头排列' THEN 1 END) as bullish_arrangement_count,
            COUNT(CASE WHEN ma_arrangement = '空头排列' THEN 1 END) as bearish_arrangement_count,
            COUNT(CASE WHEN ma_arrangement = '震荡排列' THEN 1 END) as sideways_arrangement_count
        FROM convergence_calc
        WHERE convergence_ratio <= %s
        """
        
        try:
            results = self.db.execute_query(sql, (convergence_threshold * 100,))
            return results[0] if results else {}
        except Exception as e:
            logger.error(f"获取汇总信息失败: {e}")
            return {}
    
    def get_top_convergence_stocks(self, convergence_threshold: float = 0.05, limit: int = 20) -> pd.DataFrame:
        """获取粘合度最高的股票"""
        sql = """
        WITH stock_ma AS (
            SELECT 
                sd.stock_code,
                sd.trade_date,
                sd.close_price,
                sd.volume,
                sd.change_pct,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                ) AS ma5,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS ma10,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
                ) AS ma20,
                ROW_NUMBER() OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date DESC
                ) AS rn
            FROM new_stock_daily sd
            INNER JOIN new_stock_info si ON sd.stock_code = si.stock_code
            WHERE si.status = 'active'
        ),
        convergence_calc AS (
            SELECT 
                sm.stock_code,
                si.stock_name,
                si.exchange,
                si.industry,
                sm.close_price AS current_price,
                ROUND(sm.ma5, 2) AS ma5,
                ROUND(sm.ma10, 2) AS ma10,
                ROUND(sm.ma20, 2) AS ma20,
                sm.change_pct,
                sm.volume,
                sm.trade_date,
                ROUND((GREATEST(sm.ma5, sm.ma10, sm.ma20) - LEAST(sm.ma5, sm.ma10, sm.ma20)) / 
                      GREATEST(sm.ma5, sm.ma10, sm.ma20) * 100, 2) AS convergence_ratio,
                CASE 
                    WHEN sm.ma5 > sm.ma10 AND sm.ma10 > sm.ma20 THEN '多头排列'
                    WHEN sm.ma5 < sm.ma10 AND sm.ma10 < sm.ma20 THEN '空头排列'
                    ELSE '震荡排列'
                END AS ma_arrangement
            FROM stock_ma sm
            INNER JOIN new_stock_info si ON sm.stock_code = si.stock_code
            WHERE sm.rn = 1
            AND sm.ma5 IS NOT NULL 
            AND sm.ma10 IS NOT NULL 
            AND sm.ma20 IS NOT NULL
        )
        SELECT *
        FROM convergence_calc
        WHERE convergence_ratio <= %s
        ORDER BY convergence_ratio ASC, volume DESC
        LIMIT %s
        """
        
        try:
            results = self.db.execute_query(sql, (convergence_threshold * 100, limit))
            return pd.DataFrame(results)
        except Exception as e:
            logger.error(f"获取顶级粘合股票失败: {e}")
            return pd.DataFrame()
    
    def get_industry_convergence_stats(self, convergence_threshold: float = 0.05) -> pd.DataFrame:
        """获取行业粘合统计"""
        sql = """
        WITH stock_ma AS (
            SELECT 
                sd.stock_code,
                sd.trade_date,
                sd.close_price,
                sd.volume,
                sd.change_pct,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
                ) AS ma5,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
                ) AS ma10,
                AVG(sd.close_price) OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date 
                    ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
                ) AS ma20,
                ROW_NUMBER() OVER (
                    PARTITION BY sd.stock_code 
                    ORDER BY sd.trade_date DESC
                ) AS rn
            FROM new_stock_daily sd
            INNER JOIN new_stock_info si ON sd.stock_code = si.stock_code
            WHERE si.status = 'active'
        ),
        convergence_calc AS (
            SELECT 
                si.industry,
                ROUND((GREATEST(sm.ma5, sm.ma10, sm.ma20) - LEAST(sm.ma5, sm.ma10, sm.ma20)) / 
                      GREATEST(sm.ma5, sm.ma10, sm.ma20) * 100, 2) AS convergence_ratio,
                sm.volume
            FROM stock_ma sm
            INNER JOIN new_stock_info si ON sm.stock_code = si.stock_code
            WHERE sm.rn = 1
            AND sm.ma5 IS NOT NULL 
            AND sm.ma10 IS NOT NULL 
            AND sm.ma20 IS NOT NULL
            AND si.industry IS NOT NULL
        )
        SELECT 
            industry,
            COUNT(*) as stock_count,
            ROUND(AVG(convergence_ratio), 2) as avg_convergence_ratio,
            ROUND(MIN(convergence_ratio), 2) as min_convergence_ratio,
            SUM(volume) as total_volume
        FROM convergence_calc
        WHERE convergence_ratio <= %s
        GROUP BY industry
        HAVING stock_count >= 3
        ORDER BY stock_count DESC, avg_convergence_ratio ASC
        LIMIT 15
        """
        
        try:
            results = self.db.execute_query(sql, (convergence_threshold * 100,))
            return pd.DataFrame(results)
        except Exception as e:
            logger.error(f"获取行业统计失败: {e}")
            return pd.DataFrame()
    
    def generate_report(self, convergence_threshold: float = 0.05):
        """生成分析报告"""
        print("=" * 80)
        print("📊 5日线、10日线、20日线粘合分析报告")
        print("=" * 80)
        print(f"📅 分析时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        print(f"🎯 粘合阈值: {convergence_threshold*100}%")
        print()
        
        # 获取汇总信息
        summary = self.get_convergence_summary(convergence_threshold)
        if not summary:
            print("❌ 无法获取汇总信息")
            return
        
        print("📈 总体统计:")
        print(f"   • 粘合股票总数: {summary['total_convergence_stocks']} 只")
        print(f"   • 平均粘合度: {summary['avg_convergence_ratio']:.2f}%")
        print(f"   • 最小粘合度: {summary['min_convergence_ratio']:.2f}%")
        print(f"   • 最大粘合度: {summary['max_convergence_ratio']:.2f}%")
        print(f"   • 总成交量: {summary['total_volume']:,}")
        print(f"   • 平均成交量: {summary['avg_volume']:,.0f}")
        print()
        
        print("🔥 粘合强度分布:")
        print(f"   • 极强粘合(≤1%): {summary['strong_convergence_count']} 只")
        print(f"   • 强粘合(≤2%): {summary['medium_convergence_count']} 只")
        print()
        
        print("📊 均线排列分布:")
        print(f"   • 多头排列: {summary['bullish_arrangement_count']} 只")
        print(f"   • 空头排列: {summary['bearish_arrangement_count']} 只")
        print(f"   • 震荡排列: {summary['sideways_arrangement_count']} 只")
        print()
        
        # 获取顶级粘合股票
        top_stocks = self.get_top_convergence_stocks(convergence_threshold, 15)
        if not top_stocks.empty:
            print("🏆 粘合度最高的15只股票:")
            print("-" * 100)
            print(f"{'排名':<4} {'代码':<8} {'名称':<12} {'粘合度':<8} {'排列':<8} {'5日线':<8} {'10日线':<8} {'20日线':<8} {'当前价':<8} {'涨跌幅':<8}")
            print("-" * 100)
            
            for i, (_, row) in enumerate(top_stocks.iterrows(), 1):
                print(f"{i:<4} {row['stock_code']:<8} {row['stock_name']:<12} "
                      f"{row['convergence_ratio']:<8.2f}% {row['ma_arrangement']:<8} "
                      f"{row['ma5']:<8.2f} {row['ma10']:<8.2f} {row['ma20']:<8.2f} "
                      f"{row['current_price']:<8.2f} {row['change_pct']:<8.2f}%")
            print("-" * 100)
            print()
        
        # 获取行业统计
        industry_stats = self.get_industry_convergence_stats(convergence_threshold)
        if not industry_stats.empty:
            print("🏭 行业粘合统计 (前15名):")
            print("-" * 80)
            print(f"{'行业':<20} {'股票数':<8} {'平均粘合度':<12} {'最小粘合度':<12} {'总成交量':<15}")
            print("-" * 80)
            
            for _, row in industry_stats.iterrows():
                print(f"{str(row['industry'])[:18]:<20} {row['stock_count']:<8} "
                      f"{row['avg_convergence_ratio']:<12.2f}% {row['min_convergence_ratio']:<12.2f}% "
                      f"{row['total_volume']:<15,}")
            print("-" * 80)
            print()
        
        print("💡 分析说明:")
        print("   • 粘合度 = (最高均线 - 最低均线) / 最高均线 × 100%")
        print("   • 粘合度越小，表示均线越接近，粘合程度越高")
        print("   • 均线粘合通常表示股价处于整理阶段，可能面临方向选择")
        print("   • 多头排列：5日线 > 10日线 > 20日线")
        print("   • 空头排列：5日线 < 10日线 < 20日线")
        print("   • 震荡排列：均线交叉，方向不明确")
        print()
        print("=" * 80)

def main():
    """主函数"""
    try:
        reporter = MAConvergenceReporter()
        
        # 生成不同阈值的报告
        thresholds = [0.01, 0.03, 0.05]
        
        for threshold in thresholds:
            reporter.generate_report(threshold)
            print("\n" + "="*80 + "\n")
        
    except Exception as e:
        logger.error(f"生成报告失败: {e}")
        raise

if __name__ == "__main__":
    main()
